﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

namespace syc.Fc
{
    public partial class ShengChanRiBao4 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected int[] ban1 = new int[21];
        protected int[] ban2 = new int[21];
        protected int[] ban3 = new int[21];
        protected int[] ban4 = new int[21];

        protected int[] lei1 = new int[21];
        protected int[] lei2 = new int[21];
        protected int[] lei3 = new int[21];
        protected int[] lei4 = new int[21];
        protected int[] lei5 = new int[21];

        protected double[] su = new double[9];
        protected double[] tie = new double[9];

        protected void Page_Load(object sender, EventArgs e)
        {
            if (RiQi.Text == "")
            {
                RiQi.Text = DateTime.Today.ToString("yyyy年M月d日");
                RiQi2.Text = DateTime.Today.Year.ToString() + "年" + (DateTime.Today.Month).ToString() + "月1日";
                RiQi3.Text = DateTime.Today.Year.ToString() + "年1月1日";
            }
        }
        protected void WriteToFile()
        {
            DateTime d;
            if (!DateTime.TryParse(this.RiQi.Text, out d))
            {
                d = DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + d.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("报表模板/螺纹保护器日生产报表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "四分厂螺纹保护器日生产报表";
            hssfworkbook.SummaryInformation = si;
        }

        protected void Button_Save_Click(object sender, EventArgs e)
        {

            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("螺纹保护器日生产报表");
            sheet1.GetRow(1).GetCell(0).SetCellValue(this.RiQi.Text);

            Button_Query_Click(null, null);

            int i;
            for (i = 0; i < 8; i++)
            {
                sheet1.GetRow(i + 6).GetCell(2).SetCellValue(ban1[i]);
                sheet1.GetRow(i + 6).GetCell(3).SetCellValue(lei1[i]);

                sheet1.GetRow(i + 6).GetCell(4).SetCellValue(ban2[i]);
                sheet1.GetRow(i + 6).GetCell(5).SetCellValue(lei2[i]);

                sheet1.GetRow(i + 6).GetCell(6).SetCellValue(ban3[i]);
                sheet1.GetRow(i + 6).GetCell(7).SetCellValue(lei3[i]);

                sheet1.GetRow(i + 6).GetCell(8).SetCellValue(ban4[i]);
                sheet1.GetRow(i + 6).GetCell(9).SetCellValue(lei4[i]);

                sheet1.GetRow(i + 6).GetCell(12).SetCellValue(lei5[i]);

            }
            for (i = 0; i < 4; i++)
            {
                sheet1.GetRow(4).GetCell(2 + i * 2).SetCellValue(su[i]);
                sheet1.GetRow(4).GetCell(3 + i * 2).SetCellValue(su[i + 4]);

                sheet1.GetRow(5).GetCell(2 + i * 2).SetCellValue(tie[i]);
                sheet1.GetRow(5).GetCell(3 + i * 2).SetCellValue(tie[i + 4]);

            }
            sheet1.GetRow(4).GetCell(12).SetCellValue(su[8]);
            sheet1.GetRow(5).GetCell(12).SetCellValue(tie[8]);


            sheet1.GetRow(14).GetCell(2).SetCellValue(ban1[8]+ban1[9]);
            sheet1.GetRow(15).GetCell(2).SetCellValue(ban1[10] + ban1[11]);

            sheet1.GetRow(14).GetCell(4).SetCellValue(ban2[8] + ban2[9]);
            sheet1.GetRow(15).GetCell(4).SetCellValue(ban2[10] + ban2[11]);

            sheet1.GetRow(14).GetCell(6).SetCellValue(ban3[8] + ban3[9]);
            sheet1.GetRow(15).GetCell(6).SetCellValue(ban3[10] + ban3[11]);

            sheet1.GetRow(14).GetCell(8).SetCellValue(ban4[8] + ban4[9]);
            sheet1.GetRow(15).GetCell(8).SetCellValue(ban4[10] + ban4[11]);


            sheet1.GetRow(14).GetCell(3).SetCellValue(lei1[8] + lei1[9]);
            sheet1.GetRow(15).GetCell(3).SetCellValue(lei1[10] + lei1[11]);

            sheet1.GetRow(14).GetCell(5).SetCellValue(lei2[8] + lei2[9]);
            sheet1.GetRow(15).GetCell(5).SetCellValue(lei2[10] + lei2[11]);

            sheet1.GetRow(14).GetCell(7).SetCellValue(lei3[8] + lei3[9]);
            sheet1.GetRow(15).GetCell(7).SetCellValue(lei3[10] + lei3[11]);

            sheet1.GetRow(14).GetCell(9).SetCellValue(lei4[8] + lei4[9]);
            sheet1.GetRow(15).GetCell(9).SetCellValue(lei4[10] + lei4[11]);

            sheet1.GetRow(14).GetCell(12).SetCellValue(lei5[8]+lei5[9]);
            sheet1.GetRow(15).GetCell(12).SetCellValue(lei5[10] + lei5[11]);


            sheet1.GetRow(17).GetCell(2).SetCellValue(ban1[12]);
            sheet1.GetRow(18).GetCell(2).SetCellValue(ban1[13]);

            sheet1.GetRow(17).GetCell(4).SetCellValue(ban2[12]);
            sheet1.GetRow(18).GetCell(4).SetCellValue(ban2[13]);

            sheet1.GetRow(17).GetCell(6).SetCellValue(ban3[12]);
            sheet1.GetRow(18).GetCell(6).SetCellValue(ban3[13]);

            sheet1.GetRow(17).GetCell(8).SetCellValue(ban4[12]);
            sheet1.GetRow(18).GetCell(8).SetCellValue(ban4[13]);

            sheet1.GetRow(17).GetCell(3).SetCellValue(lei1[12]);
            sheet1.GetRow(18).GetCell(3).SetCellValue(lei1[13]);

            sheet1.GetRow(17).GetCell(5).SetCellValue(lei2[12]);
            sheet1.GetRow(18).GetCell(5).SetCellValue(lei2[13]);

            sheet1.GetRow(17).GetCell(7).SetCellValue(lei3[12]);
            sheet1.GetRow(18).GetCell(7).SetCellValue(lei3[13]);

            sheet1.GetRow(17).GetCell(9).SetCellValue(lei4[12]);
            sheet1.GetRow(18).GetCell(9).SetCellValue(lei4[13]);

            sheet1.GetRow(17).GetCell(12).SetCellValue(lei5[12]);
            sheet1.GetRow(18).GetCell(12).SetCellValue(lei5[13]);


            sheet1.GetRow(20).GetCell(2).SetCellValue(ban1[14]);
            sheet1.GetRow(21).GetCell(2).SetCellValue(ban1[15]);

            sheet1.GetRow(20).GetCell(4).SetCellValue(ban2[14]);
            sheet1.GetRow(21).GetCell(4).SetCellValue(ban2[15]);

            sheet1.GetRow(20).GetCell(6).SetCellValue(ban3[14]);
            sheet1.GetRow(21).GetCell(6).SetCellValue(ban3[15]);

            sheet1.GetRow(20).GetCell(8).SetCellValue(ban4[14]);
            sheet1.GetRow(21).GetCell(8).SetCellValue(ban4[15]);

            sheet1.GetRow(20).GetCell(3).SetCellValue(lei1[14]);
            sheet1.GetRow(21).GetCell(3).SetCellValue(lei1[15]);

            sheet1.GetRow(20).GetCell(5).SetCellValue(lei2[14]);
            sheet1.GetRow(21).GetCell(5).SetCellValue(lei2[15]);

            sheet1.GetRow(20).GetCell(7).SetCellValue(lei3[14]);
            sheet1.GetRow(21).GetCell(7).SetCellValue(lei3[15]);

            sheet1.GetRow(20).GetCell(9).SetCellValue(lei4[14]);
            sheet1.GetRow(21).GetCell(9).SetCellValue(lei4[15]);

            sheet1.GetRow(20).GetCell(12).SetCellValue(lei5[14]);
            sheet1.GetRow(21).GetCell(12).SetCellValue(lei5[15]);


            //车丝废
            sheet1.GetRow(23).GetCell(2).SetCellValue(ban1[16] + ban1[17]);
            sheet1.GetRow(24).GetCell(2).SetCellValue(ban1[18] + ban1[19]);

            sheet1.GetRow(23).GetCell(4).SetCellValue(ban2[16] + ban2[17]);
            sheet1.GetRow(24).GetCell(4).SetCellValue(ban2[18] + ban2[19]);

            sheet1.GetRow(23).GetCell(6).SetCellValue(ban3[16] + ban3[17]);
            sheet1.GetRow(24).GetCell(6).SetCellValue(ban3[18] + ban3[19]);

            sheet1.GetRow(23).GetCell(8).SetCellValue(ban4[16] + ban4[17]);
            sheet1.GetRow(24).GetCell(8).SetCellValue(ban4[18] + ban4[19]);


            sheet1.GetRow(23).GetCell(3).SetCellValue(lei1[16] + lei1[17]);
            sheet1.GetRow(24).GetCell(3).SetCellValue(lei1[18] + lei1[19]);

            sheet1.GetRow(23).GetCell(5).SetCellValue(lei2[16] + lei2[17]);
            sheet1.GetRow(24).GetCell(5).SetCellValue(lei2[18] + lei2[19]);

            sheet1.GetRow(23).GetCell(7).SetCellValue(lei3[16] + lei3[17]);
            sheet1.GetRow(24).GetCell(7).SetCellValue(lei3[18] + lei3[19]);

            sheet1.GetRow(23).GetCell(9).SetCellValue(lei4[16] + lei4[17]);
            sheet1.GetRow(24).GetCell(9).SetCellValue(lei4[18] + lei4[19]);

            sheet1.GetRow(23).GetCell(12).SetCellValue(lei5[16] + lei5[17]);
            sheet1.GetRow(24).GetCell(12).SetCellValue(lei5[18] + lei5[19]);

            //填入入库套数
            sheet1.GetRow(25).GetCell(2).SetCellValue(ban1[20]);
            sheet1.GetRow(25).GetCell(4).SetCellValue(ban2[20]);
            sheet1.GetRow(25).GetCell(6).SetCellValue(ban3[20]);
            sheet1.GetRow(25).GetCell(8).SetCellValue(ban4[20]);
            sheet1.GetRow(25).GetCell(3).SetCellValue(lei1[20]);
            sheet1.GetRow(25).GetCell(5).SetCellValue(lei2[20]);
            sheet1.GetRow(25).GetCell(7).SetCellValue(lei3[20]);
            sheet1.GetRow(25).GetCell(9).SetCellValue(lei4[20]);
            sheet1.GetRow(25).GetCell(12).SetCellValue(lei5[20]);


            string op = (string)this.Session["OperatorName"];
            if (op == null)
            {
                op = "未知用户";
            }
            sheet1.GetRow(26).GetCell(11).SetCellValue("制表人："+op);

            sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {

            DateTime d1, d2, d3;
            if (!DateTime.TryParse(this.RiQi.Text, out d1))
            {
                this.Message.Text = "·请输入日期";
                return;
            }
            if (!DateTime.TryParse(this.RiQi2.Text, out d2))
            {
                this.Message.Text = "·请输入累计日期";
                return;
            }
            if (!DateTime.TryParse(this.RiQi3.Text, out d3))
            {
                this.Message.Text = "·请输入年累计日期";
                return;
            }

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            int[] p = null;

            Cmd.CommandText ="select * from [生产进度4] where [日期]='" + d1.ToShortDateString() + "'";//当日
            SqlDataReader Reader = Cmd.ExecuteReader();
            
            while (Reader.Read())
            {
                if (((int)Reader["工段"]) == 1)
                {
                    su[0] = (double)Reader["投入塑质"];
                    tie[0] = (double)Reader["投入铁质"];
                    p = ban1;
                }
                else if (((int)Reader["工段"]) == 2)
                {
                    su[1] = (double)Reader["投入塑质"];
                    tie[1] = (double)Reader["投入铁质"];
                    p = ban2;
                }
                else if (((int)Reader["工段"]) == 3)
                {
                    su[2] = (double)Reader["投入塑质"];
                    tie[2] = (double)Reader["投入铁质"];
                    p = ban3;
                }
                else if (((int)Reader["工段"]) == 4)
                {
                    su[3] = (double)Reader["投入塑质"];
                    tie[3] = (double)Reader["投入铁质"];
                    p = ban4;
                }
                p[0] = (int)Reader["内套"];
                p[1] = (int)Reader["外套"];
                p[2] = (int)Reader["内套废"];
                p[3] = (int)Reader["外套废"];

                p[4] = (int)Reader["注塑1"];
                p[5] = (int)Reader["注塑2"];
                p[6] = (int)Reader["注塑3"];
                p[7] = (int)Reader["注塑4"];

                p[8] = (int)Reader["注废1"];
                p[9] = (int)Reader["注废2"];
                p[10] = (int)Reader["注废3"];
                p[11] = (int)Reader["注废4"];

                p[12] = (int)Reader["车丝1"];
                p[13] = (int)Reader["车丝2"];
                p[14] = (int)Reader["车丝3"];
                p[15] = (int)Reader["车丝4"];

                p[16] = (int)Reader["车废1"];
                p[17] = (int)Reader["车废2"];
                p[18] = (int)Reader["车废3"];
                p[19] = (int)Reader["车废4"];

                p[20] = (int)Reader["入库"];

            }
            Reader.Close();

            Cmd.CommandText =
@"select 工段,
sum(投入塑质) as 投入塑质,
sum(投入铁质) as 投入铁质,
sum(内套) as 内套,
sum(外套) as 外套,
sum(内套废) as 内套废,
sum(外套废) as 外套废,
sum(注塑1) as 注塑1,
sum(注塑2) as 注塑2,
sum(注塑3) as 注塑3,
sum(注塑4) as 注塑4,
sum(注废1) as 注废1,
sum(注废2) as 注废2,
sum(注废3) as 注废3,
sum(注废4) as 注废4,
sum(车丝1) as 车丝1,
sum(车丝2) as 车丝2,
sum(车丝3) as 车丝3,
sum(车丝4) as 车丝4,
sum(车废1) as 车废1,
sum(车废2) as 车废2,
sum(车废3) as 车废3,
sum(车废4) as 车废4,
sum(入库) as 入库
from [生产进度4] where [日期]>='"
+ d2.ToShortDateString() + "' and [日期]<='" + d1.ToShortDateString() + "' group by 工段";//月累计
            Reader = Cmd.ExecuteReader();

            while (Reader.Read())
            {
                if (((int)Reader["工段"]) == 1)
                {
                    su[4] = (double)Reader["投入塑质"];
                    tie[4] = (double)Reader["投入铁质"];
                    p = lei1;
                }
                else if (((int)Reader["工段"]) == 2)
                {
                    su[5] = (double)Reader["投入塑质"];
                    tie[5] = (double)Reader["投入铁质"];
                    p = lei2;
                }
                else if (((int)Reader["工段"]) == 3)
                {
                    su[6] = (double)Reader["投入塑质"];
                    tie[6] = (double)Reader["投入铁质"];
                    p = lei3;
                }
                else if (((int)Reader["工段"]) == 4)
                {
                    su[7] = (double)Reader["投入塑质"];
                    tie[7] = (double)Reader["投入铁质"];
                    p = lei4;
                }
                p[0] = (int)Reader["内套"];
                p[1] = (int)Reader["外套"];
                p[2] = (int)Reader["内套废"];
                p[3] = (int)Reader["外套废"];

                p[4] = (int)Reader["注塑1"];
                p[5] = (int)Reader["注塑2"];
                p[6] = (int)Reader["注塑3"];
                p[7] = (int)Reader["注塑4"];

                p[8] = (int)Reader["注废1"];
                p[9] = (int)Reader["注废2"];
                p[10] = (int)Reader["注废3"];
                p[11] = (int)Reader["注废4"];

                p[12] = (int)Reader["车丝1"];
                p[13] = (int)Reader["车丝2"];
                p[14] = (int)Reader["车丝3"];
                p[15] = (int)Reader["车丝4"];

                p[16] = (int)Reader["车废1"];
                p[17] = (int)Reader["车废2"];
                p[18] = (int)Reader["车废3"];
                p[19] = (int)Reader["车废4"];

                p[20] = (int)Reader["入库"];

            }

            Reader.Close();




            Cmd.CommandText =
@"select
sum(投入塑质) as 投入塑质,
sum(投入铁质) as 投入铁质,
sum(内套) as 内套,
sum(外套) as 外套,
sum(内套废) as 内套废,
sum(外套废) as 外套废,
sum(注塑1) as 注塑1,
sum(注塑2) as 注塑2,
sum(注塑3) as 注塑3,
sum(注塑4) as 注塑4,
sum(注废1) as 注废1,
sum(注废2) as 注废2,
sum(注废3) as 注废3,
sum(注废4) as 注废4,
sum(车丝1) as 车丝1,
sum(车丝2) as 车丝2,
sum(车丝3) as 车丝3,
sum(车丝4) as 车丝4,
sum(车废1) as 车废1,
sum(车废2) as 车废2,
sum(车废3) as 车废3,
sum(车废4) as 车废4,
sum(入库) as 入库
from [生产进度4] where [日期]>='" + d3.ToShortDateString() + "' and [日期]<='" + d1.ToShortDateString() + "'";//年累计
            Reader = Cmd.ExecuteReader();

            while (Reader.Read())
            {

                su[8] = (double)Reader["投入塑质"];
                tie[8] = (double)Reader["投入铁质"];
                p = lei5;

                p[0] = (int)Reader["内套"];
                p[1] = (int)Reader["外套"];
                p[2] = (int)Reader["内套废"];
                p[3] = (int)Reader["外套废"];

                p[4] = (int)Reader["注塑1"];
                p[5] = (int)Reader["注塑2"];
                p[6] = (int)Reader["注塑3"];
                p[7] = (int)Reader["注塑4"];

                p[8] = (int)Reader["注废1"];
                p[9] = (int)Reader["注废2"];
                p[10] = (int)Reader["注废3"];
                p[11] = (int)Reader["注废4"];

                p[12] = (int)Reader["车丝1"];
                p[13] = (int)Reader["车丝2"];
                p[14] = (int)Reader["车丝3"];
                p[15] = (int)Reader["车丝4"];

                p[16] = (int)Reader["车废1"];
                p[17] = (int)Reader["车废2"];
                p[18] = (int)Reader["车废3"];
                p[19] = (int)Reader["车废4"];

                p[20] = (int)Reader["入库"];

            }

            Reader.Close();

            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }



    }
}
